const Router = require('koa-router');
const tool = require("../../tools/tool");
const router = new Router();
const {db, QueryTypes} = require('../../models/db')
const {sql} = require("@sequelize/core");
// 采购汇总-商品
router.post('/receipt/listrei', async ctx => {
    let user = ctx.user
    let body = ctx.request.body
    let formData = body.formData
    let tenantId = user.tenantId;
    let beginDate = formData.beginDate ? formData.beginDate: '2001-01-01';
    let endDate = formData.endDate?formData.endDate:tool.getDay();
    let buId = formData.buId?formData.buId:0;
    let accId = formData.accId?formData.accId:0;
    // let salesId = formData.salesId?formData.salesId:0;
    // let goodCatCode = formData.goodCatCode?formData.goodCatCode:'';
    // let invId = formData.invId?formData.invId:'';
    let billNo = formData.billNo?formData.billNo:'';
    let srcBillNo = formData.srcBillNo?formData.srcBillNo:'';
    let billState = formData.billState?formData.billState:'';
    let currentPage = body.currentPage?body.currentPage:1;
    let pageSize = body.pageSize?body.pageSize:200;
    let offset = (currentPage-1) * pageSize;

    let excel = body.excel
    let sqls = `select distinct a.id,a.printNo,a.billDate,a.buName,a.billNo,a.disAmount,a.amount,a.billStateSort,billStateName,a.cbyName,a.billState,a.billType,
      a.totalAmount,a.remark,a.createdAt from v_main a 
          inner join invoice_receipt b on a.tenantId=b.tenantId and a.id=b.iid where a.tenantId=${tenantId} and a.billType='RECEIPT' 
     `
    if(billState !== 'all') {
        sqls += ` and a.billState='${billState}' `
    }
    else {
        sqls += ` and a.billState in('draft','accounted') `
    }
    if(beginDate) {
        sqls += ` and a.billDate>='${beginDate}' `
    }
    if(beginDate) {
        sqls += ` and a.billDate<='${endDate}' `
    }
    if(buId) {
        sqls += ` and a.buId=${buId} `
    }
    if(billNo) {
        sqls += ` and a.billNo like '%${billNo}%' `
    }
    if(srcBillNo) {
        sqls += ` and b.srcBillNo like '%${srcBillNo}%' `
    }
    if(accId) {
        sqls += ` and (a.accId=${accId} or a.accId1=${accId}) `
    }

    if(excel) {
        let list = await db.query(sqls, {type: QueryTypes.SELECT})
        return tool.doExcelOut(ctx, list)
    }
    else {
        let totalModel = await db.query(`select count(*) total from (${sqls}) a `, {type: QueryTypes.SELECT, plain: true})
        sqls = ` select * from (${sqls}) a order by billStateSort, billDate desc offset ${offset} rows fetch next ${pageSize} rows only;`
        let list = await db.query(sqls, {type: QueryTypes.SELECT})
        return ctx.suc('', {
            total: totalModel?totalModel.total: 0,
            list: list
        })
    }
})





module.exports = router;